"""
    This function will extract Glassdoor's international pay data.
"""

import pandas as pd

def extractInternationalSalaries(conn, gocConfidence):


    query = '''
               SELECT
                      fs.FK_userId, fs.FK_employerId, fs.yearOfSalary,
                      an.name as countryName,
                      rs.basePayPeriodCode, rs.basePayAmount, ac1.FNK_currencyCode as baseCurrency,
                      rs.cashBonusPayPeriodId, rs.cashBonusAmount, ac2.FNK_currencyCode as cashCurrency,
                      rs.stockBonusPayPeriodId, rs.stockBonusAmount, ac3.FNK_currencyCode as stockCurrency,
                      rs.profitSharingPayPeriodId, rs.profitSharingAmount, ac4.FNK_currencyCode as priftCurrency,
                      rs.salesCommissionPayPeriodId, rs.salesCommissionAmount, ac5.FNK_currencyCode as salesCurrency,
                      rs.tipsPayPeriodId, rs.tipsAmount, ac6.FNK_currencyCode as tipsCurrency,
                      rs.reviewDateTime,
                      cast (case
                      			when rs.basePayPeriodCode = 'MONTHLY' then rs.basePayAmount * 12
                      			when rs.basePayPeriodCode = 'HOURLY' then rs.basePayAmount * 2000
                      			else rs.basePayAmount end
                      		as float) as baseSalary,
                      rs.employmentStatusCode,
                      fs.yearsOfRelevantExpNumber,
                      ac.name as city,
                      ac.stateAbbreviation as state,
                      am.uniquename as metro,
                      u.birthYear, u.highestEducation,
                      coalesce(u.gender,rs.gender) as gender,
                      ind.industryName, ind.sectorName,
                      jt.jobTitle,
                      emp.employeesTotalNum, emp.employerTypeCode, emp.name, emp.shortName,
                      fs.isCurrentJobFlag,
                      case
                      	when jt.gocConfidence > %f THEN goc.occupation END
                      as GOC
              from
              	reporting..f_salary fs with(nolock) --Salaries, inflation adjusted.
              inner join
              	bizproj..review_salary rs with(nolock) --Raw salaries, not inflation adjusted.
              	on fs.FK_reviewId  = rs.PK_id --BAKER NOTES (if you have a where statement on this table, don't do outer join then)
              left join
              	reporting..a_employerSummary ae with(nolock) --Industry and employer info.
              	on fs.FK_employerId = ae.FK_employerId
              left join
              	bizproj..GDUser u with(nolock) --User data.
              	on fs.FK_userId = u.PK_id
              left join
              	bizproj..industry ind with(nolock) --Sector information.
              	on ae.industryNK = ind.NK_industryId
              left join
              	bizproj..jobTitle jt with(nolock)
              	on fs.FK_jobTitleId = jt.PK_id
              left join
              	bizproj..glassdoorOccupationCategory goc with(nolock)
              	on jt.FK_gocId = goc.PK_id
              left join
              	bizproj..glassdoorOccupationCategory mgoc with(nolock)
              	on jt.FK_mgocId = mgoc.PK_id
              left join
              	bizproj..employer emp with(nolock)
              	on ae.FK_employerId = emp.PK_id
              left join
              	atlas..atlas_city ac with(nolock)
                  on ac.PK_id = fs.FK_cityId
              left join
              	atlas..atlas_metro am with(nolock)
                  on am.PK_id = ac.FK_metroId
              left join
              	atlas..atlas_country an with(nolock)
                  on an.PK_id = ac.FK_countryId
              left join
              	atlas..atlas_currency ac1 with(nolock)
                  on ac1.PK_id = rs.FK_basePayCurrencyId
              left join
              	atlas..atlas_currency ac2 with(nolock)
                  on ac2.PK_id = rs.FK_cashBonusCurrencyId
              left join
              	atlas..atlas_currency ac3 with(nolock)
                  on ac3.PK_id = rs.FK_stockBonusCurrencyId
              left join
              	atlas..atlas_currency ac4 with(nolock)
                  on ac4.PK_id = rs.FK_profitSharingCurrencyId
              left join
              	atlas..atlas_currency ac5 with(nolock)
                  on ac5.PK_id = rs.FK_salesCommissionCurrencyId
              left join
              	atlas..atlas_currency ac6 with(nolock)
                  on ac6.PK_id = rs.FK_tipsCurrencyId
              where
                  fs.FK_approvalStatusId = 1 -- Approved salaries only.
                  and fs.FK_salarySourceId = 1 -- Only Salaries from clean source (no H1B)
              order by FK_userId, yearOfSalary, reviewDateTime
            ''' % (gocConfidence)

    return pd.read_sql(query,conn)
